﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.IO.Compression;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Web;
using Utility;

namespace BLL
{
    public class ServiceHelper
    {
        //服务连接地址
        private string ServiceURL = "";// ConfigurationManager.AppSettings["ServiceURL"].Trim();

        private string MesServiceURL = ConfigurationManager.AppSettings["MesServiceURL"].Trim();//短信连接

                                                                                                /// <summary>
                                                                                                /// 客户端获取服务对象
                                                                                                /// </summary>
                                                                                                /// <returns></returns>
        public BLL.ThinkerService.Service getService()
        {
            BLL.ThinkerService.Service service = new BLL.ThinkerService.Service();

            service.Credentials = System.Net.CredentialCache.DefaultCredentials;

            service.Timeout = 3600 * 1000;

            service.Url = ServiceURL;

            return service;
        }

        /// <summary>
        /// 客户端获取服务对象
        /// </summary>
        /// <returns></returns>
        public BLL.ThinkerService.Service getMesService()
        {
            BLL.ThinkerService.Service service = new BLL.ThinkerService.Service();

            ServiceURL = MesServiceURL;

            service.Credentials = System.Net.CredentialCache.DefaultCredentials;

            service.Timeout = 3600 * 1000;

            service.Url = ServiceURL;

            return service;
        }

        public static DataTable getDataTable(byte[] data)
        {
            //将字节流数组解压缩

            //byte[] buffer = UnZipClass.Decompress(zipBuffer);

            MemoryStream ms = new MemoryStream(data);

            Stream zipStream = null;

            zipStream = new System.IO.Compression.GZipStream(ms, CompressionMode.Decompress, true);

            //实例化一个序列化对象
            IFormatter formatter = new BinaryFormatter();

            DataTable result = (DataTable)formatter.Deserialize(zipStream);

            return result;
        }

        public static YQHL.GS.App app;

        public static void RunGS()
        {
            if (app == null)
            {
                app = new YQHL.GS.App();
                app.BankEvent = () =>
                {
                    new YX_BANK_LISTBLL().AppGo();
                };
                app.Run();
            }
            else
            {
                app.Run();
            }
        }

        #region Service

        /// <summary>
        /// 常规查询,返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="win"></param>
        /// <param name="method"></param>
        /// <param name="operatename"></param>
        /// <param name="logonPerson"></param>
        /// <returns></returns>
        public DataTable Query(string sql, string win, string method, string operatename, string logonPerson)
        {
            DataTable dt = new DataTable();
            try
            {
                OracleHelp ohelper = new OracleHelp();
                dt = ohelper.GetDataTable(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }

            return dt;
        }

        /// <summary>
        /// 常规查询,返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="win"></param>
        /// <param name="method"></param>
        /// <param name="operatename"></param>
        /// <returns></returns>
        public DataSet ExcuteDataSetGer(string sql, string win, string method, string operatename)
        {
            DataSet dt = new DataSet();
            try
            {
                OracleHelp ohelper = new OracleHelp();
                dt = ohelper.GetDataSet(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }

            return dt;
        }

        /// <summary>
        /// 获取数据集条数
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="win">窗体名</param>
        /// <param name="method">客户端方法名</param>
        /// <param name="operatename">客户端操作名</param>
        /// <returns>数据集条数</returns>
        public int GetCountByOrc(string sql, string win, string method, string operatename)
        {
            int result = 0;
            try
            {
                OracleHelp ohelper = new OracleHelp();
                result = ohelper.getCount(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }
            return result;
        }

        /// <summary>
        /// 数据操作
        /// </summary>
        /// <param name="sql">SQL语句-INSERT-UPDATE-DELETE </param>
        /// <param name="win">窗体名</param>
        /// <param name="method">客户端方法名</param>
        /// <param name="operatename">客户端操作名</param>
        /// <returns>受影响的行数</returns>
        public int ExcuteOperation(string sql, string win, string method, string operatename)
        {
            int result = 0;
            try
            {
                OracleHelp ohelper = new OracleHelp();
                result = ohelper.ExecuteDataBase(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }
            return result;
        }

        /// <summary>
        /// 压缩传输查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="win"></param>
        /// <param name="method"></param>
        /// <param name="operatename"></param>
        /// <param name="logonPerson"></param>
        /// <returns></returns>
        public byte[] ExcuteQueryTex(string sql, string win, string method, string operatename, string logonPerson)
        {
            DataTable dt = new DataTable();
            try
            {
                //写文本文件
                try
                {
                    //writeLog(sql, logonPerson);//查询不写日志
                }
                catch
                {
                }
                OracleHelp ohelper = new OracleHelp();
                dt = ohelper.GetDataTable(sql);
                MemoryStream ms = new MemoryStream();

                IFormatter formatter = new BinaryFormatter();

                if (dt == null) { dt = new DataTable(); }

                formatter.Serialize(ms, dt);

                //将字节流转化为数组放到byte数组中
                byte[] buffer = ms.ToArray();

                //将字节zip压缩后放在字节数组中
                byte[] Zipbuffer = Compress(buffer);
                return Zipbuffer;
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }

            return null;
        }

        /// <summary>
        /// 压缩数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public byte[] Compress(byte[] data)
        {
            //实例化一个内存流对象
            MemoryStream ms = new MemoryStream();
            //实例化一个流对象
            Stream zipStream = null;

            //GZipStream提供用于压缩和解压缩流的方法和属性。

            //CompressionMode.Compress表示压缩
            //ms创建一个压缩流
            zipStream = new System.IO.Compression.GZipStream(ms, CompressionMode.Compress, true);
            //将data中的字节流写入该流中
            zipStream.Write(data, 0, data.Length);

            ///关闭该流
            zipStream.Close();
            //将内存流的位置指向开  始
            ms.Position = 0;
            //声明一个内存流长度的字节数组
            byte[] compressed_data = new byte[ms.Length];
            //从当前流中读取字节流写入compressed_data 中
            ms.Read(compressed_data, 0, int.Parse(ms.Length.ToString()));
            return compressed_data;
        }

        private static Object chargeTran = new Object();

        /// <summary>
        /// 存储过程执行带返回值
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="sp">存储过程所需参数</param>
        /// <param name="win">窗体名</param>
        /// <param name="method">客户端方法名</param>
        /// <param name="operatename">客户端操作名</param>
        /// <returns>返回值</returns>
        public string ExcuteProcEX(string procName, DataTable pdt, string win, string method, string operatename, int outIndex)
        {
            string result = "null";
            try
            {
                OracleParameter[] sp = null;
                lock (chargeTran)
                {
                    //加锁处理并发

                    #region 执行

                    if (pdt != null)
                    {
                        sp = new OracleParameter[pdt.Rows.Count];
                        OracleParameter singlesp = new OracleParameter();
                        int count = 0;
                        foreach (DataRow dr in pdt.Rows)
                        {
                            if (dr["dir"].ToString().Equals("1"))
                            {
                                singlesp = new OracleParameter(dr["pname"].ToString(), OracleDbType.Varchar2, int.Parse(dr["size"].ToString()), dr["pvalue"].ToString(), ParameterDirection.Input);
                                //OracleType.VarChar,
                                //OracleDbType.Varchar2,
                                // singlesp = new OracleParameter(dr["pname"].ToString(), OracleType.VarChar, int.Parse(dr["size"].ToString()), ParameterDirection.Input, "pvalue", DataRowVersion.Default, true, dr["pvalue"].ToString());
                            }
                            else if (dr["dir"].ToString().Equals("0"))
                            {
                                singlesp = new OracleParameter(dr["pname"].ToString(), OracleDbType.Varchar2, int.Parse(dr["size"].ToString()), dr["pvalue"].ToString(), ParameterDirection.Output);
                                // singlesp = new OracleParameter(dr["pname"].ToString(), OracleType.VarChar, int.Parse(dr["size"].ToString()), ParameterDirection.Output, "pvalue", DataRowVersion.Default, true, dr["pvalue"].ToString());
                            }
                            else if (dr["dir"].ToString().Equals("2"))
                            {
                                singlesp = new OracleParameter(dr["pname"].ToString(), OracleDbType.Varchar2, int.Parse(dr["size"].ToString()), dr["pvalue"].ToString(), ParameterDirection.InputOutput);
                                // singlesp = new OracleParameter(dr["pname"].ToString(), OracleType.VarChar, int.Parse(dr["size"].ToString()), ParameterDirection.InputOutput, "pvalue", DataRowVersion.Default, true, dr["pvalue"].ToString());
                            }

                            sp[count] = singlesp;
                            count++;
                        }
                    }
                    OracleHelp ohelper = new OracleHelp();
                    result = ohelper.RunSQLEX(procName, sp, outIndex);

                    if (result != "1")
                    {
                        //   此处写入错误日志
                        StringBuilder xsb = new StringBuilder();
                        xsb.Append("<?xml version=\"1.0\"?>\r\n");
                        xsb.Append("<root>\r\n");
                        xsb.Append("<ErrorInfo>" + result + "</ErrorInfo>\r\n");
                        xsb.Append("<Window>" + win + "</Window>\r\n");
                        xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                        xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                        xsb.Append("<ExcuteSQL>" + procName + "</ExcuteSQL>\r\n");
                        xsb.Append("<DataFor>ExcuteProc</DataFor>\r\n");
                        xsb.Append("</root>\r\n");
                        CreateErrorDOC(xsb.ToString());
                    }

                    #endregion 执行
                }
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + procName + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteProc</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }
            return result;
        }

        /// <summary>
        /// 复合SQL语句执行
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="win"></param>
        /// <param name="method"></param>
        /// <param name="operatename"></param>
        /// <param name="logonPerson"></param>
        /// <returns></returns>
        public int SQLSExcute(string sql, string win, string method, string operatename, string logonPerson)
        {
            int result = 0;

            try
            {
                try
                {
                    //  writeLog(sql, logonPerson);
                }
                catch
                {
                }
                OracleHelp ohelper = new OracleHelp();
                result = ohelper.ExecuteDataBaseEx(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }

            return result;
        }

        public int SQLSExcute(List<string> sql, string win, string method, string operatename, string logonPerson)
        {
            int result = 0;

            try
            {
                try
                {
                    //  writeLog(sql, logonPerson);
                }
                catch
                {
                }
                OracleHelp ohelper = new OracleHelp();
                result = ohelper.ExecuteDataBaseEx(sql);
            }
            catch (Exception ex)
            {
                //此处写入错误日志
                StringBuilder xsb = new StringBuilder();
                xsb.Append("<?xml version=\"1.0\"?>\r\n");
                xsb.Append("<root>\r\n");
                xsb.Append("<ErrorInfo>" + ex.Message + "</ErrorInfo>\r\n");
                xsb.Append("<Window>" + win + "</Window>\r\n");
                xsb.Append("<ErrorMethod>" + method + "</ErrorMethod>\r\n");
                xsb.Append("<Operatename>" + operatename + "</Operatename>\r\n");
                xsb.Append("<ExcuteSQL>" + sql + "</ExcuteSQL>\r\n");
                xsb.Append("<DataFor>ExcuteQuery</DataFor>\r\n");
                xsb.Append("</root>\r\n");
                CreateErrorDOC(xsb.ToString());
            }

            return result;
        }

        public int SQLSExcute(List<Tuple<string, List<Tuple<string, string, string>>>> sqlArry, string win, string method, string operatename, string logonPerson)
        {
            try
            {
                OracleHelp ohelper = new OracleHelp();
                var result = 0;
                using (var conn = ohelper.GetConecttion())
                {
                    foreach (var item in sqlArry)
                    {
                        result += ohelper.NoQuery(conn, item.Item1, item.Item2);
                    }
                }
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static Object xmllogTran = new Object();

        /// <summary>
        /// 错误日志写入
        /// </summary>
        public string CreateErrorDOC(string xmlSTR)
        {
            lock (xmllogTran)
            {
                string url = BuildPath("~/Error/");//Server.MapPath(@"~/Error/");
                string filename = "Error" + DateTime.Now.ToString("yyyyMMddss") + DateTime.Now.Millisecond.ToString() + ".xml";
                string fullpath = url + filename;
                File.AppendAllText(fullpath, xmlSTR);
            }

            return "1";
        }

        private static string BuildPath(string path)
        {
            //如果有path就转换为绝对路径
            if (!Path.IsPathRooted(path))
            {
                path = HttpContext.Current.Server.MapPath(path);
            }
            //如果没有`"~/Upload/PROOther"`这个文件夹就创建
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            return Path.GetFullPath(path);
        }

        #endregion Service
    }
}